﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Odbc;

namespace SQL_Manager.Database_Drivers
{
    sealed public class Driver_MySQL : Database
    {
        // Note: If we change ODBC versions we NEED to change the version number here
        private string driver = "MySQL ODBC 5.1 Driver";
        private OdbcConnection _Connection = null;

        public Driver_MySQL(string username, string password, string db, string host)
        {
            this._Connection = new OdbcConnection(string.Format("driver={0};server={1};database={2};uid={3};pwd={4};", this.driver, host, db, username, password));

            this._Connection.Open();

            instance = this;
            //TODO: Exception handling =(
            //  we need to find a way to determine what went wrong:
            //      a) user, pass was wrong
            //      b) incorrect OR non-existant database supplied
            //      c) invalid host
            //      d) ODBC error, not users fault
        }

        //
        // Closes connection to database IF connected
        //
        public override void close()
        {
            if (this._Connection.State == ConnectionState.Open)
            {
                this._Connection.Close();
            }
        }
        
        //
        // Simple method of detecting if we are connected or not
        //
        public override bool connected()
        {
            if (this._Connection != null)
            {
                return (this._Connection.State == ConnectionState.Open);
            }
            return false;
        }

        //
        // Execute a query against the database
        // - Returns # of rows affected; obviously 0 would mean something went wrong
        //
        public override int execute(string query)
        {
            return new OdbcCommand(query, this._Connection).ExecuteNonQuery();
        }

        //
        // Queries for multiple rows
        //
        public override List<Hashtable> queryAll(string query)
        {
            List<Hashtable> data = new List<Hashtable>();

            OdbcCommand com = new OdbcCommand(query, this._Connection);
            OdbcDataReader reader = com.ExecuteReader();

            int i;
            Hashtable n;

            while (reader.Read())
            {
                n = new Hashtable();

                for (i = 0; i < reader.FieldCount; ++i)
                {
                    n.Add(reader.GetName(i), reader[i]);
                }

                data.Add(n);
            }

            return data;
        }

        //
        // Queries for multiple rows
        // This assigns 'table' by reference, no need for return value
        //
        public override void queryAll(string query, out List<Hashtable> table)
        {
            table = new List<Hashtable>();

            OdbcCommand com = new OdbcCommand(query, this._Connection);
            OdbcDataReader reader = com.ExecuteReader();

            int i;
            Hashtable n;

            while (reader.Read())
            {
                n = new Hashtable();

                for (i = 0; i < reader.FieldCount; ++i)
                {
                    n.Add(reader.GetName(i), reader[i]);
                }

                table.Add(n);
            }
        }

        //
        // Queries for a single row
        //
        public override Hashtable query(string query)
        {
            Hashtable data = new Hashtable();

            OdbcCommand com = new OdbcCommand(query, this._Connection);
            OdbcDataReader reader = com.ExecuteReader(CommandBehavior.SingleRow);
            reader.Read();

            for (int i = 0; i < reader.FieldCount; ++i)
            {
                data.Add(reader.GetName(i), reader[i]);
            }

            return data;
        }

        //
        // Queries for a single row
        // This assigns 'table' by reference, no need for return value
        //
        public override void query(string query, out Hashtable table)
        {
            OdbcCommand com = new OdbcCommand(query, this._Connection);
            OdbcDataReader reader = com.ExecuteReader(CommandBehavior.SingleRow);
            reader.Read();

            if (reader.HasRows)
            {
                table = new Hashtable();
                for (int i = 0; i < reader.FieldCount; ++i)
                {
                    table.Add(reader.GetName(i), reader[i]);
                }

                return;
            }

            table = null;
        }        
    }
}
